package edu.jhu.cs.oose.scheduleit.dao.impl;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateTemplate;

import edu.jhu.cs.oose.scheduleit.dao.UserDao;
import edu.jhu.cs.oose.scheduleit.util.JoinStatus;
import edu.jhu.cs.oose.scheduleit.vo.Group;
import edu.jhu.cs.oose.scheduleit.vo.User;
/**
 * The implementation class of the UserDao interface
 * @author oosegroup2
 *
 */
public class UserDaoImpl implements UserDao{
	
	private HibernateTemplate hibernateTemplate;

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
	
	@Override
	public void save(User user){
		this.hibernateTemplate.saveOrUpdate(user.getUserInfo());
		this.hibernateTemplate.saveOrUpdate(user);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public User get(String email, String password){
		String queryString = "select distinct u from User u join u.userInfo uio where uio.email = '"+email+"' and u.password ='"+password+"'";
		List<User> results = this.hibernateTemplate.find(queryString);
		return results.size() > 0? results.get(0): null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public User get(String email){
		String queryString = "select distinct u from User u join u.userInfo uio where uio.email = '"+email+"'";
		List<User> results = this.hibernateTemplate.find(queryString);
		return results.size() > 0? results.get(0): null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public int countGroup(User user, JoinStatus status) {
		String attribute = null;
		switch (status){
			case OWNED:
			case JOINED:
				attribute ="joinedGroups";
				break;
			case APPLIED:
				attribute ="appliedGroups";
				break;
		}
		StringBuilder queryString = new StringBuilder();
		queryString.append("SELECT COUNT(g) FROM User as u inner join u.");
		queryString.append(attribute);
		queryString.append(" as g WHERE u.id = ?");
		if (status == JoinStatus.OWNED){
			queryString.append(" and g.host.id = ");
			queryString.append(user.getUserInfo().getId());
		}else if (status == JoinStatus.JOINED){
			queryString.append(" and g.host.id != ");
			queryString.append(user.getUserInfo().getId());
		}
		List result = this.hibernateTemplate.find(queryString.toString(),new Object[]{user.getId()});
		return Integer.parseInt(result.get(0).toString());
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Group> getGroup(User user, JoinStatus status, int offset,
			int limit) {
		String attribute = null;
		switch (status){
			case OWNED:
			case JOINED:
				attribute ="joinedGroups";
				break;
			case APPLIED:
				attribute ="appliedGroups";
				break;
		}
		StringBuilder queryString = new StringBuilder("SELECT g FROM User as u inner join u.");
		queryString.append(attribute);
		queryString.append(" as g WHERE u.id = ");
		queryString.append(user.getId());
		if (status == JoinStatus.OWNED){
			queryString.append(" and g.host.id = ");
			queryString.append(user.getUserInfo().getId());
		}else if (status == JoinStatus.JOINED){
			queryString.append(" and g.host.id != ");
			queryString.append(user.getUserInfo().getId());
		}
		
		queryString.append(" order by u.id");
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			
			Query hqlQuery = session.createQuery(queryString.toString());
			hqlQuery.setFirstResult(offset);
			if (limit <=0){
				hqlQuery.setMaxResults(Integer.MAX_VALUE);
			}else{
				hqlQuery.setMaxResults(limit);
			}
			return (List<Group>) hqlQuery.list();	
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}

	@Override
	public void addGroup(User user, Group group, JoinStatus status) {
		String userTable = null;
		String userColumn = null;
		StringBuilder queryString = null;
		switch(status){
			case JOINED:
				userTable = "User_joinedTeams";
				userColumn = "joinedGroups_id";
				break;
			case APPLIED:
				userTable = "User_appliedTeams";
				userColumn = "appliedGroups_id";
				break;
		}
		
		queryString = new StringBuilder("INSERT INTO ");
		queryString.append(userTable);
		queryString.append(" (User_id,");
		queryString.append(userColumn);
		queryString.append(") values(");
		queryString.append(user.getId());
		queryString.append(",");
		queryString.append(group.getId());
		queryString.append(")");

		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();		
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
		
	}

	@Override
	public void deleteGroup(Group group, User user, JoinStatus status) {
		String userTable = null;
		String userColumn = null;
		StringBuilder queryString = null;
		switch(status){
			case JOINED:
				userTable = "User_joinedTeams";
				userColumn = "joinedGroups_id";
				break;
			case APPLIED:
				userTable = "User_appliedTeams";
				userColumn = "appliedGroups_id";
				break;
		}
		
		queryString = new StringBuilder("DELETE FROM ");
		queryString.append(userTable);
		queryString.append(" where ");
		queryString.append(" User_id = ");
		queryString.append(user.getId());
		queryString.append(" and ");
		queryString.append(userColumn);
		queryString.append(" = ");
		queryString.append(group.getId());

		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();	
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
		
	}
	
}
